/*
	For the Assembly process we are cosidering the Vendor Lot Number as 
	Lot Number
*/
Alter proc sp_create_update_data_entry_assembly_lot (
	
	@PROCESSSTEPID		INT,
	@LOCATIONID		INT,	
	@FAB_LOT_NUMBER 	VARCHAR(50),
	@QTY_IN 		INT, 
	@QTY_OUT 		INT, 
	@PO_No			VARCHAR(50),
	@SHOP_ORDER_NO		INT,
	@DATE_IN 		DATETIME,
	@DATE_OUT 		DATETIME,
	@VENDOR_NAME 		VARCHAR(50),
	@WAFER_ID 		VARCHAR(100),
	@LOT_CATEGORY 		VARCHAR(10),
	@VENDOR_LOT_NUMBER 	VARCHAR(50),
	@LOT_DATE_CODE 		VARCHAR(50),
	@LOT_STATUS 		VARCHAR(50),
	@LOT_COMMENTS 		VARCHAR(500),
	@USER_NAME			VARCHAR(50),
	@PREV_SHIP_LOTID	INT,
	@PREV_PROCESS_LOTID INT ) 
as
begin
	
	DECLARE @LOT_ID 		INT
	DECLARE @VENDOR_ID 		INT
	DECLARE @PURCHASE_ORDER_ID 	INT
	DECLARE @THIS_LOT_STATUS 	VARCHAR(50)
		
	DECLARE @DATE_TIME 	DATETIME
	DECLARE @START_DATE 	DATETIME
	DECLARE @END_DATE 	DATETIME

	
	
	select @this_lot_status = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @processStepId and Location_ID = @locationId and Lot_Number = @VENDOR_LOT_NUMBER
	
	if @lot_id is null -- Check if the lot exists
	begin -- Create Lot
		
		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Qty_Out, Date_In, Date_Out,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Date_Code, Lot_Comments, Po_Number, Shop_Order_Number, LOT_DROP_OUT, USER_NAME ) 
			values (@processStepId, @locationId, @VENDOR_LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @qty_in, @qty_out, @date_in, @date_out, @lot_status, CURRENT_TIMESTAMP, 0, @lot_category, @Lot_Date_Code, @lot_Comments, @Po_No, @Shop_Order_No, 0 , @USER_NAME )
		select @lot_id = @@IDENTITY

		insert into Assembly_Lot( Lot_Id, Timestamp) values (@lot_id, Current_timestamp)
		
	end
	else
	begin 

		declare @temp_varchar varchar(20)
		set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
		set @start_date = convert(datetime, @temp_varchar)
		set @temp_varchar = null
		set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
		set @end_date = convert(datetime, @temp_varchar)
	
		/* Updating into lot History Table
		   Started Here*/
		select @date_time = (Lot_Table_History.timestamp) from Lot_Table_History where Lot_ID = @lot_id
		
			if @date_time between @start_date and @end_date
			begin
			update Lot_Table_History set Lot_Current_Status = @LOT_STATUS, Qty_In = @qty_in, Qty_Out = @Qty_Out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Date_Code = @Lot_Date_Code, Lot_Category = @Lot_category, LOT_DROP_OUT = 0,USER_NAME = @USER_NAME where Lot_ID = @lot_id
			end
			else
			begin	
				insert into Lot_Table_History(Lot_ID, Processstep_Id, Location_Id,Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Lot_Entered_By, Lot_Manual_Hold, Lot_Category, Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, Date_In,Date_Out,Stage_Name,Stage_Position,Lot_Current_Status,Lot_Comments,Timestamp, LOT_DROP_OUT, USER_NAME )
	 			(select Lot_ID, Processstep_Id, Location_Id, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Lot_Entered_By, Lot_Manual_Hold, Lot_Category, Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, Date_In, Date_Out, Stage_Name, Stage_Position, Lot_Current_Status,Lot_Comments,CURRENT_TIMESTAMP, 0, USER_NAME from Lot_Table where Lot_id = @lot_id)
			end
		/*Ended Here*/

		update Lot_Table set Lot_Current_Status = @LOT_STATUS, Qty_In = @QTY_IN, Qty_Out = @QTY_OUT, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Date_Code = @Lot_Date_Code, Lot_Category = @Lot_category, Po_Number = @PO_No, Shop_Order_Number = @SHOP_ORDER_NO, LOT_DROP_OUT = 0 ,USER_NAME = @USER_NAME where Lot_ID = @lot_id
		
	end
	--Inserting into History table when lot is having dateout and Quantity Out		
	if @QTY_OUT is not null and @DATE_OUT is not null
	begin
		
		update Lot_Table set Lot_Current_Status = 'Close', Qty_In = @qty_in, Qty_Out = @Qty_Out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Date_Code = @Lot_Date_Code, Lot_Category = @Lot_category, Po_number = @Po_No, Shop_Order_Number = @Shop_Order_No, LOT_DROP_OUT = 0, USER_NAME = @USER_NAME where Lot_ID = @lot_id

		insert into Lot_Table_History (Lot_Id, ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Qty_Out, Date_In, Date_Out,Lot_Current_Status, Timestamp, Lot_Entered_By, Lot_Category, Po_number, Shop_Order_Number, LOT_DROP_OUT, USER_NAME ) 
		values ( @lot_id, @processStepId, @locationId, @VENDOR_LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @qty_in, @qty_out, @date_in, @date_out, 'Close', CURRENT_TIMESTAMP, 0, @lot_category, @Po_No, @Shop_Order_No, 0, @USER_NAME)

	end

	IF @PREV_SHIP_LOTID > 0 OR @PREV_SHIP_LOTID = -1
	BEGIN
			UPDATE SHIPPED_LOT SET STATUS = 'Close', Ship_Status = 1 , Close_Date = CURRENT_TIMESTAMP WHERE SHIPPEDLOT_ID = @PREV_SHIP_LOTID
	END
	ELSE
	BEGIN
		/* Closing Transit Lots and Previous Process Lots*/
		Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS  @VENDOR_LOT_NUMBER, @FAB_LOT_NUMBER, @Qty_In, @date_in 
	END	
end
